(java.lang.Class:forName "org.postgresql.Driver")
(define (make-connection) (java.sql.DriverManager:getConnection "jdbc:postgresql://xxx" "xxx" "xxx"))
(define (string-null? str)
  (= ((as <String> str):length) 0))

(define (inc i) (+ i 1))
(define (string-split any-str . rest)
    ; maxsplit is a positive number
(let ((str (if (instance? any-str <String>)
              (<gnu.lists.FString> (as <String> any-str))
              any-str)))
  (define (split-by-whitespace str maxsplit)
    (define (skip-ws i yet-to-split-count)
      (cond
        ((>= i (string-length str)) '())
        ((char-whitespace? (string-ref str i))
          (skip-ws (inc i) yet-to-split-count))
        (else (scan-beg-word (inc i) i yet-to-split-count))))
    (define (scan-beg-word i from yet-to-split-count)
      (cond
        ((zero? yet-to-split-count)
          (cons (substring str from (string-length str)) '()))
        (else (scan-word i from yet-to-split-count))))
    (define (scan-word i from yet-to-split-count)
      (cond
        ((>= i (string-length str))
          (cons (substring str from i) '()))
        ((char-whitespace? (string-ref str i))
          (cons (substring str from i) 
            (skip-ws (inc i) (- yet-to-split-count 1))))
        (else (scan-word (inc i) from yet-to-split-count))))
    (skip-ws 0 (- maxsplit 1)))

    ; maxsplit is a positive number
    ; str is not empty
  (define (split-by-charset str delimeters maxsplit)
    (define (scan-beg-word from yet-to-split-count)
      (cond
        ((>= from (string-length str)) '(""))
        ((zero? yet-to-split-count)
          (cons (substring str from (string-length str)) '()))
        (else (scan-word from from yet-to-split-count))))
    (define (scan-word i from yet-to-split-count)
      (cond
        ((>= i (string-length str))
          (cons (substring str from i) '()))
        ((memq (string-ref str i) delimeters)
          (cons (substring str from i) 
            (scan-beg-word (inc i) (- yet-to-split-count 1))))
        (else (scan-word (inc i) from yet-to-split-count))))
    (scan-beg-word 0 (- maxsplit 1)))

      ; resolver of overloading...
      ; if omitted, maxsplit defaults to
      ; (inc (string-length str))
  (if (string-null? str) '()
    (if (null? rest) 
      (split-by-whitespace str (inc (string-length str)))
      (let ((charset (car rest))
          (maxsplit
            (if (pair? (cdr rest)) (cadr rest) (inc (string-length str)))))
        (cond 
          ((not (positive? maxsplit)) '())
          ((null? charset) (split-by-whitespace str maxsplit))
          (else (split-by-charset str charset maxsplit))))))))

(define (write-db-file connection :: <java.sql.Connection> file-name utterance-id)
  (let ((f :: <java.io.File> (<java.io.File> (file-name:toString)))
        (result (db-query connection "SELECT data FROM audio WHERE utterance_id=? ORDER BY chunk" utterance-id)))
    (try-finally
      (try-catch
        (letrec ((fos :: <java.io.FileOutputStream> (<java.io.FileOutputStream> f))
                 (next (lambda (chunk)
                         (if (result:next)
                           (let ((b (result:getBytes "data")))
                               (fos:write (as <byte[]> b))
                               (next (+ chunk 1)))))))
          (try-finally
            (next 1)
            (fos:close)))
        (e <java.lang.Exception>
          (begin
            (throw e))))
      (result:close))))
(define (db-statement connection :: <java.sql.Connection> str . args)
  (letrec ((statement :: <java.sql.PreparedStatement> (connection:prepareStatement str))
           (next (lambda (arg-index) 
                   (if (< arg-index (length args))
                     (let ((arg (list-ref args arg-index))
                           (param-index (+ arg-index 1)))
                       (if (or (number? arg)
                               (instance? arg java.lang.Integer)
                               (instance? arg java.lang.Float)
                               (instance? arg java.lang.Double))
                         (if (or (integer? arg)
                                 (instance? arg java.lang.Integer))
                           (statement:setLong param-index arg)
                           (statement:setDouble param-index arg)))
                       (if (string? arg)
                         (statement:setString param-index arg))
                       (next (+ arg-index 1)))))))
    (next 0)
    (try-finally
      (try-catch
        (begin
          (statement:execute)
          (statement:close))
        (e <java.lang.Exception>
          (begin
            (newline)
            (display "error executing statement:")
            (display str)
            (throw e))))
       (statement:close))))

(define (db-statement-ignore-fail connection str)
  (try-catch
    (begin
      (db-statement connection str)
      #t)
    (e <java.lang.Exception> #f)))
(define (db-query connection :: <java.sql.Connection> str . args) :: <java.sql.ResultSet>
  (letrec ((statement :: <java.sql.PreparedStatement> (connection:prepareStatement str))
           (next (lambda (arg-index) 
                   (if (< arg-index (length args))
                     (let ((arg (list-ref args arg-index))
                           (param-index (+ arg-index 1)))
                       (if (or (number? arg) (instance? arg <java.lang.Integer>))
                         (if (or (integer? arg) (instance? arg <java.lang.Integer>))
                           (statement:setInt param-index arg)
                           (statement:setDouble param-index arg))
                         (if (string? arg)
                           (statement:setString param-index arg)
                           (if (eq? (arg:getClass) <java.sql.Timestamp>)
                             (statement:setTimestamp param-index arg)
                             (throw (java.lang.Exception (string-append "do not know how to insert:" (arg:getClass)))))))
                       (next (+ arg-index 1)))))))
    (next 0)
    (try-catch
      (statement:executeQuery)
      (e <java.lang.Exception>
        (begin
          (newline)
          (display "error executing statement:")
          (display str)
          (throw e))))))

(define (create-postgres-tables connection)
  (db-statement-ignore-fail connection "DROP TABLE sample")
  (db-statement-ignore-fail connection "DROP TABLE test")
  (db-statement-ignore-fail connection "DROP TABLE data_name")
  (db-statement connection "CREATE TABLE data_name
    ( data_name_id SERIAL PRIMARY KEY,
      name VARCHAR(50) NOT NULL UNIQUE)")
  (db-statement connection "CREATE TABLE test
    ( test_id SERIAL PRIMARY KEY,
      name VARCHAR(50) NOT NULL UNIQUE)")
  (db-statement connection "CREATE TABLE sample
    ( sample_id SERIAL PRIMARY KEY,
      time BIGINT NOT NULL,
      test_id INTEGER NOT NULL REFERENCES test, 
      data_name_id INTEGER NOT NULL REFERENCES data_name, 
      value REAL NOT NULL)"))

(define (get-string rec-result key)
  (<gnu.lists.FString> (as <String> (rec-result:getString key))))
(define (get-int rec-result column)
  (if (number? column)
    ((rec-result:getInt (<java.lang.Integer> column)):intValue)
    ((rec-result:getInt column):intValue)))
(define (get-float rec-result column)
  (if (number? column)
    ((rec-result:getFloat (<java.lang.Integer> column)):floatValue)
    ((rec-result:getFloat column):floatValue)))

(define (find-test-set-id connection :: <java.sql.Connection> n)
  (if (number? n)
    (let ((result (db-query connection "SELECT test-set_id FROM run WHERE run_id=?" n)))
      (if (result:next)
        (get-int result "_id")
        0))
    (let ((result (db-query connection "SELECT _id FROM test WHERE name=?" n)))
      (if (result:next)
        (get-int result "_id")
        0))))

(define (get-data-name connection data-name-id)
  (let ((result (db-query connection "SELECT name FROM data_name WHERE data_name_id=?" data-name-id)))
    (if (result:next)
      (get-string result "name")
      0)))

(define (get-test connection test-id)
  (let ((result (db-query connection "SELECT name FROM test WHERE test_id=?" test-id)))
    (if (result:next)
      (get-string result "name")
      0)))

(define (get-data-name-id connection data-name) :: <int>
  (let ((result (db-query connection "SELECT data_name_id FROM data_name WHERE name=?" data-name)))
    (if (result:next)
      (get-int result "data_name_id")
      0)))

(define (get-test-id connection test) :: <int>
  (let ((result (db-query connection "SELECT test_id FROM test WHERE name=?" test)))
    (if (result:next)
      (get-int result "test_id")
      0)))

(define (add-sample connection test data-name time value)
  (let ((test-id (if (integer? test) test (get-test-id connection test)))
        (data-name-id (if (integer? data-name) data-name (get-data-name-id connection data-name))))
    (db-statement connection "INSERT INTO sample (time,test_id,data_name_id,value) VALUES (?,?,?,?)" time test-id data-name-id value)))

(define (add-test connection test-name)
  (if (= (get-test-id connection test-name) 0)
    (begin
      (db-statement connection "INSERT INTO test (name) VALUES (?)" test-name)
      (get-test-id connection test-name))
    0))

(define (add-data-name connection data-name)
  (if (= (get-data-name-id connection data-name) 0)
    (begin
      (db-statement connection "INSERT INTO data_name (name) VALUES (?)" data-name)
      (get-data-name-id connection data-name))
    0))


;test combo box
;sub plot
;  list box of data names
;  adjust vertical scaling number field or auto
;time frame
;  window seconds
;  end slider or realtime
;save to svg
;
